package com.example.demo.work;

import com.example.demo.config.MySQLConfig;
import com.example.demo.work.util.ExportExcelUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Row;
import org.springframework.beans.factory.InitializingBean;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.*;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 类
 *
 * @author Xiaopeng Jiang
 * @date 2020/7/14 12:01
 */
@Service
public class SqlInsertXlsx implements InitializingBean {
    @Resource
    MySQLConfig mySQLConfig;
    static String urlMysql = null;
    // 数据库的用户名与密码，需要根据自己的设置
    static String user = null;
    static String pass = null;
    //执行项
    static String[] needDoneTabales = null;
    //排除
    static List<String> deleteTables = null;
    //统计未找到的表
    static Map<String, String> tablesMap = null;
    //统计未找到的表
    static String outPath = null;

    @Override
    public void afterPropertiesSet() throws Exception {
        urlMysql = mySQLConfig.getUrlMysql();
        user = mySQLConfig.getUser();
        pass = mySQLConfig.getPass();
        needDoneTabales = mySQLConfig.getNeedDoneTabales().split(",");
        deleteTables = Arrays.asList(mySQLConfig.getDeleteTables().split(",")
        );
        outPath = mySQLConfig.getOutPath();
        tablesMap = intToMap(needDoneTabales);
        sqlTable2Xlsx(needDoneTabales);
        System.out.println("--------------完成-------------------");
    }


    /**
     * 数组 转成 map形式
     *
     * @param array 被操作数组
     * @return 目标Map
     */
    public static Map<String, String> intToMap(String[] array) {
        Map<String, String> map = new HashMap<String, String>();
        for (String s : array) {
            map.put(s, s);
        }
        return map;
    }

    /**
     * @param tableNames 传入一个字符串数组
     */
    public static void sqlTable2Xlsx(String[] tableNames) {
        String DATABASE = urlMysql.substring(urlMysql.lastIndexOf("/") + 1, urlMysql.indexOf("?"));
        // 实例化 导出Excel工具
        ExportExcelUtils exportExcelUtils = new ExportExcelUtils(null, null);


        Connection conn = null;
        Statement stmt = null;
        try {
            // 注册 JDBC 驱动
            Class.forName("com.mysql.jdbc.Driver");
            // 打开链接
            System.out.println("连接数据库...");
            conn = DriverManager.getConnection(urlMysql, user, pass);
            // 执行查询
            System.out.println(" 实例化Statement对象...");
            stmt = conn.createStatement();
            ResultSet rs = null;
            for (int i = 0; i < tableNames.length; i++) {
                if (deleteTables.contains(tableNames[i])) {
                    tablesMap.remove(tableNames[i]);
                    continue;
                }
                // 获取表注释
                String getTableCommentSql =
                        "SELECT table_name,table_comment FROM information_schema.TABLES WHERE table_schema = '" + DATABASE + "'and table_name = '" + tableNames[i] + "'";
                rs = stmt.executeQuery(getTableCommentSql);
                rs.next();
                String table_comment = null;
                String table_name = null;
                try {
                    table_comment = rs.getString("table_comment");
                    table_name = rs.getString("table_name");
                } catch (SQLException e) {
                    System.out.println("------------获取表注释--SQLException-------------------table=" + tableNames[i]);
                    e.printStackTrace();
                    continue;
                }
                // 获取表主键
                // SELECT COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE table_schema = 'pcs' and table_name = 'bank_contract_info' and CONSTRAINT_NAME='PRIMARY'; -- 获取主键记录
                String getPrimaryColumnSql = "SELECT COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE table_schema = '" + DATABASE + "'and table_name = '" + tableNames[i] + "'" + " and CONSTRAINT_NAME='PRIMARY'";
                rs = stmt.executeQuery(getPrimaryColumnSql);
                rs.next();
                String primaryColumn = "";
                try {
                    primaryColumn = rs.getString("COLUMN_NAME");
                } catch (SQLException e) {
                    System.out.println("------------获取表主键--SQLException-------------------table=" + tableNames[i]);
                    e.printStackTrace();
                }
                tablesMap.remove(tableNames[i]);
                // 获取表字段
                String getColumnSql = "select COLUMN_NAME,COLUMN_TYPE,COLUMN_DEFAULT,IS_NULLABLE,COLUMN_KEY,COLUMN_COMMENT from information_schema.COLUMNS where table_name = '" + tableNames[i] + "'";
                rs = stmt.executeQuery(getColumnSql);

                //----------隔6行
                if (i > 0) {
                    exportExcelUtils.addRow(6);
                }
                //----------两行标题
                List<String> headerList1 = Arrays.asList("表名", table_comment, table_name, "", "", "", "", "", "");
                exportExcelUtils.initializeHeaderList(headerList1);
                List<String> headerList2 = Arrays.asList("列名", "字段名", "数据类型",
                        "长度", "小数长度", "是否主键", "是否可为空", "列说明", "默认值");
                exportExcelUtils.initializeHeaderList(headerList2);
                // 展开结果集数据库
                while (rs.next()) {
                    Row row = exportExcelUtils.addRow();
                    // 通过字段检索
                    //1 列名备注
                    String Field_COMMENT = rs.getString("COLUMN_COMMENT");
                    //2 字段名
                    String COLUMN_NAME = rs.getString("COLUMN_NAME");
                    //3 数据类型
                    String COLUMN_TYPE = rs.getString("COLUMN_TYPE");
                    //4 长度
                    String COLUMN_TYPE_LEN = "";
                    int indexOf4 = COLUMN_TYPE.indexOf("(");
                    int lastIndexOf4 = COLUMN_TYPE.lastIndexOf(")");
                    if (indexOf4 != -1 && lastIndexOf4 != -1) {
                        String substring4 = COLUMN_TYPE.substring(indexOf4 + 1, lastIndexOf4);
                        COLUMN_TYPE_LEN = substring4;
                    }
                    //5 小数长度
                    String SMALL_LEN = "";
                    if (COLUMN_TYPE_LEN.contains(",")) {
                        String[] decimals = COLUMN_TYPE_LEN.split(",");
                        SMALL_LEN = decimals[1];
                        COLUMN_TYPE_LEN = decimals[0];
                    }
                    //6 是否主键
                    String COLUMN_PRIMARY = "";
                    if (COLUMN_NAME.equals(primaryColumn)) {
                        COLUMN_PRIMARY = "Y";
                    }
                    //7 是否可为空
                    String IS_NULLABLE = rs.getString("IS_NULLABLE");
                    if ("YES".equals(IS_NULLABLE)) {
                        IS_NULLABLE = "Y";
                    } else {
                        IS_NULLABLE = "N";
                    }

                    //8 列说明  字段名和描述按|分割
                    String COLUMN_COMMENT = "";
                    if (Field_COMMENT.contains("#")) {
                        String[] comments = Field_COMMENT.split("#");
                        COLUMN_COMMENT = comments[1];
                        Field_COMMENT = comments[0];
                    } else {
                        COLUMN_COMMENT = Field_COMMENT;
                    }

                    //9 默认值
                    String COLUMN_DEFAULT = rs.getString("COLUMN_DEFAULT");
                    if (COLUMN_DEFAULT == null) {
                        COLUMN_DEFAULT = "NULL";
                    } else if (StringUtils.isBlank(COLUMN_DEFAULT)) {
                        COLUMN_DEFAULT = "";
                    }

                    //放入Xlsx
                    //1 列名
                    exportExcelUtils.addCell(row, 0, Field_COMMENT);
                    //2 字段名
                    exportExcelUtils.addCell(row, 1, COLUMN_NAME);
                    //3 数据类型
                    exportExcelUtils.addCell(row, 2, COLUMN_TYPE);
                    //4 长度
                    exportExcelUtils.addCell(row, 3, COLUMN_TYPE_LEN);
                    //5 小数长度
                    exportExcelUtils.addCell(row, 4, SMALL_LEN);
                    //6 是否主键
                    exportExcelUtils.addCell(row, 5, COLUMN_PRIMARY);
                    //7 是否可为空
                    exportExcelUtils.addCell(row, 6, IS_NULLABLE);
                    //8 列说明
                    exportExcelUtils.addCell(row, 7, COLUMN_COMMENT);
                    //9 默认值
                    exportExcelUtils.addCell(row, 8, COLUMN_DEFAULT);

                }
            }
            FileOutputStream out = null;
            try {
                out = new FileOutputStream(outPath + "表结构信息-" + System.currentTimeMillis() + ".xlsx");
                exportExcelUtils.write(out);
            } catch (IOException e) {
                System.out.println(e.toString());
            } finally {
                System.out.println("--------------完成-------------------tablesList=" + tablesMap.keySet());
            }

            exportExcelUtils.dispose();
            // 完成后关闭
            rs.close();
            stmt.close();
            conn.close();
            out.close();
            return;
        } catch (SQLException se) {
            // 处理 JDBC 错误
            se.printStackTrace();
        } catch (Exception e) {
            // 处理 Class.forName 错误
            e.printStackTrace();
        } finally {
            // 关闭资源
            try {
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException se2) {
            }// 什么都不做
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException se) {
                se.printStackTrace();
            }
        }
        System.out.println("完成!");
    }

}